|
我正在尝试使用新的工单数据更新当前工作工单数据的工作簿。我在底部嵌套了for each循环，以检查票号是否相等。如果它找到匹配，它应该用新数据更新一些单元格。如果该票证不在我的票证列表中，则会将新票证添加到底部。不断发生的是，即使是电子表格中的票据，它也会不断地将来自newData的所有票据添加到currentData的底部。我认为问题最终在于这些嵌套的for循环中的逻辑，但我不能确切地指出我做错了什么。 Sub getNewData()
Dim newData As Workbook
Dim ndLastRow As Long
Dim currentData As Workbook
Dim cdLastRow As Long
Dim ndRangeToCheck As Range
Dim cdRangeToCheck As Range
Dim ndRow As Long
Dim cdRow As Long
Set newData = Workbooks.Open("C:\Users\\Documents\newData.xlsx")
Set currentData = ThisWorkbook
' Assign last row and the range to compare for each workbook
newData.Worksheets("Incident List").Range("A2").Select
With ActiveSheet
ndLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Set ndRangeToCheck = newData.Worksheets("Incident List").Range("A2", Cells(ndLastRow, "A"))
currentData.Worksheets("Incident List").Activate
With ActiveSheet
cdLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
Set cdRangeToCheck = currentData.Worksheets("Incident List").Range("B2", Cells(cdLastRow, "B"))
' Iterate through to compare Incident #s between workbooks
Dim rout As Range
Dim rin As Range
Dim match As Boolean
For Each rout In ndRangeToCheck.Cells
match = False
For Each rin In cdRangeToCheck.Cells
If Cells(rin.Row, rin.Column).Value = Cells(rout.Row, rout.Column).Value Then
match = True
ndRow = rout.Row
cdRow = rin.Row
currentData.Worksheets("Incident List").Cells(cdRow, "L").Value = newData.Worksheets("Incident List").Cells(ndRow, "D").Value
currentData.Worksheets("Incident List").Cells(cdRow, "O").Value = newData.Worksheets("Incident List").Cells(ndRow, "F").Value
currentData.Worksheets("Incident List").Cells(cdRow, "P").Value = newData.Worksheets("Incident List").Cells(ndRow, "G").Value
currentData.Worksheets("Incident List").Cells(cdRow, "Q").Value = newData.Worksheets("Incident List").Cells(ndRow, "H").Value
currentData.Worksheets("Incident List").Cells(cdRow, "S").Value = newData.Worksheets("Incident List").Cells(ndRow, "L").Value
currentData.Worksheets("Incident List").Cells(cdRow, "T").Value = newData.Worksheets("Incident List").Cells(ndRow, "N").Value
currentData.Worksheets("Incident List").Rows(rin.Row).Borders.LineStyle = xlContinuous
Exit For
End If
Next rin
If match = False Then
ndRow = rout.Row
currentData.Worksheets("Incident List").Cells(cdLastRow, "B").Offset(1, 0).Value = newData.Worksheets("Incident List").Cells(ndRow, "A").Value
currentData.Worksheets("Incident List").Cells(cdLastRow, "B").Offset(1, 0).NumberFormat = "0"
currentData.Worksheets("Incident List").Cells(cdLastRow, "L").Offset(1, 0).Value = newData.Worksheets("Incident List").Cells(ndRow, "D").Value
currentData.Worksheets("Incident List").Cells(cdLastRow, "O").Offset(1, 0).Value = newData.Worksheets("Incident List").Cells(ndRow, "F").Value
currentData.Worksheets("Incident List").Cells(cdLastRow, "P").Offset(1, 0).Value = newData.Worksheets("Incident List").Cells(ndRow, "G").Value
currentData.Worksheets("Incident List").Cells(cdLastRow, "Q").Offset(1, 0).Value = newData.Worksheets("Incident List").Cells(ndRow, "H").Value
currentData.Worksheets("Incident List").Cells(cdLastRow, "S").Offset(1, 0).Value = newData.Worksheets("Incident List").Cells(ndRow, "L").Value
currentData.Worksheets("Incident List").Cells(cdLastRow, "T").Offset(1, 0).Value = newData.Worksheets("Incident List").Cells(ndRow, "N").Value
currentData.Worksheets("Incident List").Cells(cdLastRow, "F").Offset(1, 0).Value = newData.Worksheets("Incident List").Cells(ndRow, "C").Value
currentData.Worksheets("Incident List").Cells(cdLastRow, "M").Offset(1, 0).Value = newData.Worksheets("Incident List").Cells(ndRow, "E").Value
currentData.Worksheets("Incident List").Cells(cdLastRow, "M").Offset(1, 0).NumberFormat = "m/d/yyyy"
currentData.Worksheets("Incident List").Rows(cdLastRow).Offset(1, 0).Borders.LineStyle = xlContinuous
' Reset cdLastRow
currentData.Worksheets("Incident List").Activate
With ActiveSheet
cdLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
End If
Next rout
newData.Close
End Sub
|